package health.carousel.connection;

import health.carousel.data.PositionDetails;
import org.codehaus.jackson.JsonNode;
import org.codehaus.jackson.map.ObjectMapper;
import org.codehaus.jackson.map.annotate.JsonSerialize;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.mail.Address;
import javax.mail.MessagingException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

public  class MailCommunicator {
    private static final Logger log = LoggerFactory.getLogger(MailCommunicator.class);

    public static final String DB_USER_NAME = "emailparse";
    public static final String DB_PASSWORD = "mW3f3tu9qmwya5qA";
    public static final String DB_URL = "jdbc:mysql://hc02.cloudapp.net:3306/emailparse";

    public static final String INSERT_CONTENT_QUERY = "insert into tb_content (" +
            "content, " +
            "content_type) values (?, ?)";

    public static final String INSERT_MAIL_QUERY = "insert into tb_mail (" +
            "mail_from, " +
            "subject, " +
            "sent_date, " +
            "content_id) values (?, ?, ?, ?)";

    public static final String INSERT_CONTENT_DETAILS_QUERY = "insert into tb_content_details (" +
            "content_id, " +
            "original_position_text, " +
            "details_map, " +
            "quantity, " +
            "shift_time, " +
            "facility, " +
            "classification, " +
            "start_date, " +
            "requestor, " +
            "additional_details) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    public void putContentDataIntoDatabase(Address address,
                                                  String subject,
                                                  Date sentDate,
                                                  String content,
                                                  List<PositionDetails> detailsList) throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException, IOException, MessagingException {
        log("Putting mail details into database...");


        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = DriverManager.getConnection(DB_URL, DB_USER_NAME, DB_PASSWORD);

        log("    Database connection established");
        ResultSet rs = insertContentToDatabase(content, conn);

        if (rs.next()) {
            int contentId = rs.getInt(1);
            log("    Current content id is: " + contentId);

            insertMailToDatabase(address, subject, sentDate, conn, contentId);


            for (PositionDetails details : detailsList) {
                insertContentDetailsToDatabase(conn, contentId, details);
            }

        } else {
            throw new IllegalArgumentException("The following statement with execution parameter " +
                    "'RETURN_GENERATED_KEYS' did not return id of generated key: " +
                    "'insert into tb_content (content, content_type) values (?, ?)'");
        }

        conn.close();
    }

    private void insertContentDetailsToDatabase(Connection conn, int contentId, PositionDetails details)
            throws MessagingException, IOException, SQLException {
        // create the mysql insert preparedStatement
        PreparedStatement preparedStmt;
        preparedStmt = conn.prepareStatement(INSERT_CONTENT_DETAILS_QUERY);
        preparedStmt.setInt(1, contentId);
        preparedStmt.setString(2, details.getInitialLine());
        preparedStmt.setString(3, toJson(details));
        preparedStmt.setInt(4, details.getQuantity());
        preparedStmt.setString(5, details.getShiftTime());
        preparedStmt.setString(6, details.getFacility());
        preparedStmt.setString(7, details.getClassification());
        preparedStmt.setString(8, details.getStartDate());
        preparedStmt.setString(9, details.getRequestor());
        preparedStmt.setString(10, details.getAdditionalDetails());

        // execute the preparedStatement
        preparedStmt.execute();
        log("    Insert into tb_content_details executed successfully, line added: '" + details.getInitialLine() + "'");
    }

    private void insertMailToDatabase(Address address, String subject, Date sentDate, Connection conn, int contetnId) throws SQLException {
        // create the mysql insert prepared statement
        PreparedStatement preparedStmt;
        preparedStmt = conn.prepareStatement(INSERT_MAIL_QUERY);
        preparedStmt.setString(1, address.toString());
        preparedStmt.setString(2, subject);
        preparedStmt.setTimestamp(3, new Timestamp(sentDate.getTime()));
        preparedStmt.setInt(4, contetnId);

        // execute the prepared statement
        preparedStmt.execute();
        log("    Insert into tb_mail executed successfully");
    }

    private ResultSet insertContentToDatabase(String content, Connection conn) throws SQLException {
        // create the mysql insert prepared statement
        PreparedStatement preparedStmt = conn.prepareStatement(INSERT_CONTENT_QUERY, Statement.RETURN_GENERATED_KEYS);
        preparedStmt.setString(1, content);
        preparedStmt.setString(2, "html");

        // execute the prepared statement
        preparedStmt.execute();
        log("    Insert into tb_content executed successfully");

        return preparedStmt.getGeneratedKeys();
    }

    private void log(String message){
        log.info(message);
    }

    private String toJson(PositionDetails details) {
        ObjectMapper mapper = new ObjectMapper();
        mapper.setSerializationInclusion(JsonSerialize.Inclusion.NON_NULL);
        JsonNode jsonDetails = mapper.valueToTree(details);
        return jsonDetails.toString();
    }

}
